package com.baijia.util;

import com.baijia.commons.lang.utils.collection.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class JdbcUtil {

    private static Logger log = LoggerFactory.getLogger(JdbcUtil.class);

    private static final int DEF_BATCH_SIZE = 1000;

    public static Integer getInt(ResultSet rs, int index) throws SQLException {
        BigDecimal value = rs.getBigDecimal(index);
        if (value == null) {
            return null;
        }
        return value.intValue();
    }

    public static Long getLong(ResultSet rs, int index) throws SQLException {
        BigDecimal value = rs.getBigDecimal(index);
        if (value == null) {
            return null;
        }
        return value.longValue();
    }

    public static Double getDouble(ResultSet rs, int index) throws SQLException {
        BigDecimal value = rs.getBigDecimal(index);
        if (value == null) {
            return null;
        }
        return value.doubleValue();
    }

    public static String getString(ResultSet rs, int index) throws SQLException {
        return rs.getString(index);
    }

    public static Date getTimestamp(ResultSet rs, int index) throws SQLException {
        Timestamp timestamp = rs.getTimestamp(index);
        if (timestamp == null) {
            return null;
        }
        return new Date(timestamp.getTime());
    }

    public static Date getTimestamp(ResultSet rs, int index, Calendar calendar) throws SQLException {
        Timestamp timestamp = rs.getTimestamp(index);
        if (timestamp == null) {
            return null;
        }
        calendar.setTimeInMillis(timestamp.getTime());
        return calendar.getTime();
    }

    public static void setInt(PreparedStatement ps, int index, Integer value) throws SQLException {
        if (value == null) {
            ps.setNull(index, Types.INTEGER);
        } else {
            ps.setInt(index, value);
        }
    }

    public static void setLong(PreparedStatement ps, int index, Long value) throws SQLException {
        if (value == null) {
            ps.setNull(index, Types.BIGINT);
        } else {
            ps.setLong(index, value);
        }
    }

    public static void setDouble(PreparedStatement ps, int index, Double value) throws SQLException {
        if (value == null) {
            ps.setNull(index, Types.BIGINT);
        } else {
            ps.setDouble(index, value);
        }
    }

    public static void setString(PreparedStatement ps, int index, String value) throws SQLException {
        if (value == null) {
            ps.setNull(index, Types.VARCHAR);
        } else {
            ps.setString(index, value);
        }
    }

    public static void setTimestamp(PreparedStatement ps, int index, Date value) throws SQLException {
        if (value == null) {
            ps.setNull(index, Types.TIMESTAMP);
        } else {
            ps.setTimestamp(index, new Timestamp(value.getTime()));
        }
    }

    public static <T> void updateWithoutException(JdbcTemplate jdbcTemplate, String sql, final Collection<T> batchArgs,
        final ParameterizedPreparedStatementSetter<T> pss) {
        updateWithoutException(jdbcTemplate, sql, batchArgs, DEF_BATCH_SIZE, 0, pss);
    }

    public static <T> void updateWithoutException(JdbcTemplate jdbcTemplate, String sql, final Collection<T> batchArgs,
        final int batchSize, final ParameterizedPreparedStatementSetter<T> pss) {
        updateWithoutException(jdbcTemplate, sql, batchArgs, batchSize, 0, pss);
    }

    public static <T> void updateWithoutException(JdbcTemplate jdbcTemplate, String sql, final Collection<T> batchArgs,
        final int batchSize, final long sleepTime, final ParameterizedPreparedStatementSetter<T> pss) {
        List<List<T>> splitArgs = CollectionUtils.split(batchArgs, batchSize);
        for (Collection<T> args : splitArgs) {
            try {
                jdbcTemplate.batchUpdate(sql, args, batchSize, pss);
            } catch (Exception e) {
                log.warn("batch update sql[" + sql + "] args.size[" + args.size() + "] error!", e);
                // 批量更新失败，逐条更新
                for (final T arg : args) {
                    try {
                        jdbcTemplate.update(sql, new PreparedStatementSetter() {

                            @Override
                            public void setValues(PreparedStatement ps) throws SQLException {
                                pss.setValues(ps, arg);
                            }
                        });
                    } catch (Exception e1) {
                        log.error("update sql[" + sql + "] arg[" + arg + "] error!", e1);
                    }
                }
            }
            if (sleepTime > 0) {
                try {
                    Thread.sleep(sleepTime);
                } catch (InterruptedException e) {
                }
            }
        }
    }

    public static <T> void update(JdbcTemplate jdbcTemplate, String sql, final Collection<T> batchArgs,
        final ParameterizedPreparedStatementSetter<T> pss) {
        update(jdbcTemplate, sql, batchArgs, DEF_BATCH_SIZE, pss);
    }

    public static <T> void update(JdbcTemplate jdbcTemplate, String sql, final Collection<T> batchArgs,
        final int batchSize, final ParameterizedPreparedStatementSetter<T> pss) {
        List<List<T>> splitArgs = CollectionUtils.split(batchArgs, batchSize);
        for (Collection<T> args : splitArgs) {
            jdbcTemplate.batchUpdate(sql, args, batchSize, pss);
        }
    }

    public static <T extends Comparable<T>> void updateWithSort(JdbcTemplate jdbcTemplate, String sql,
        final Collection<T> batchArgs, final ParameterizedPreparedStatementSetter<T> pss) {
        updateWithSort(jdbcTemplate, sql, batchArgs, DEF_BATCH_SIZE, pss);
    }

    public static <T extends Comparable<T>> void updateWithSort(JdbcTemplate jdbcTemplate, String sql,
        final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss) {
        List<T> list = new ArrayList<>(batchArgs);
        Collections.sort(list);
        update(jdbcTemplate, sql, list, batchSize, pss);
    }

    public static <T extends Comparable<T>> void updateWithSort(NamedParameterJdbcTemplate namedParameterJdbcTemplate,
        String sql, final Map<String, Object> paramMap, final String listParaName, final Collection<T> listPara) {
        updateWithSort(namedParameterJdbcTemplate, sql, paramMap, DEF_BATCH_SIZE, listParaName, listPara);
    }

    public static <T extends Comparable<T>> void updateWithSort(NamedParameterJdbcTemplate namedParameterJdbcTemplate,
        String sql, final Map<String, Object> paramMap, final int batchSize, final String listParaName,
        final Collection<T> listPara) {
        List<T> list = new ArrayList<>(listPara);
        Collections.sort(list);
        List<List<T>> splitArgs = CollectionUtils.split(list, batchSize);
        for (Collection<T> args : splitArgs) {
            paramMap.put(listParaName, args);
            namedParameterJdbcTemplate.update(sql, paramMap);
        }
    }

    public static boolean appendCause(boolean needWhere, StringBuilder sql, String cause) {
        if (needWhere) {
            sql.append(" where");
            needWhere = false;
        } else {
            sql.append(" and");
        }
        sql.append(cause);
        return needWhere;
    }

    /**
     * 自动生成insert into …… on duplicate key update …… 的SQL
     * 
     * @param baseTable 基本表名，不能为空
     * @param tablePrefix 表名前缀，可以为空
     * @param tableSuffix 表名后缀，可以为空
     * @param dimItems 维度字段（唯一索引字段除ID主键）,不建议为空
     * @param baseItems 基本字段，建议除ID主键，唯一索引之外的字段，不可为空
     * @return
     */
    public static String genInsOnUpdateSql(String baseTable, String tablePrefix, String tableSuffix, String[] dimItems,
        String[] baseItems) {
        StringBuilder sql = new StringBuilder("insert into ");
        if (StringUtils.isNotBlank(tablePrefix)) {
            sql.append(tablePrefix);
        }
        sql.append(baseTable);
        if (StringUtils.isNotBlank(tableSuffix)) {
            sql.append(tableSuffix);
        }
        sql.append("(");
        if (dimItems != null) {
            for (String item : dimItems) {
                sql.append(item).append(",");
            }
        }
        for (String item : baseItems) {
            sql.append(item).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") values (");
        int size = (dimItems != null ? dimItems.length : 0) + baseItems.length;
        for (int i = 0; i < size; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") on duplicate key update ");
        for (String item : baseItems) {
            sql.append(item).append("=?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        return sql.toString();
    }

    /**
     * 自动生成查询SQL
     * 
     * @param baseTable 基本表名，不能为空
     * @param tablePrefix 表名前缀，可以为空
     * @param tableSuffix 表名后缀，可以为空
     * @param dimItems 维度字段（唯一索引字段除ID主键）,可以为空
     * @param whereSql 查询SQL的where条件,必须以空格和where开头
     * @return
     */
    public static String genSelectSql(String baseTable, String tablePrefix, String tableSuffix, String[] dimItems,
        String[] items, String whereSql) {
        StringBuilder sql = new StringBuilder("select ");
        for (String item : dimItems) {
            sql.append(item).append(",");
        }
        for (String item : items) {
            sql.append(item).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" from ");
        if (StringUtils.isNotBlank(tablePrefix)) {
            sql.append(tablePrefix);
        }
        sql.append(baseTable);
        if (StringUtils.isNotBlank(tableSuffix)) {
            sql.append(tableSuffix);
        }
        if (StringUtils.isNotBlank(whereSql)) {
            sql.append(whereSql);
        }
        return sql.toString();
    }
}
